package tvv.levelup.addressDAO.controller;

import tvv.levelup.addressDAO.model.Address;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by VLAD on 12.05.14.
 */

public class AddressDAO implements IAddressData
{

    @Override
    public List<Address> getAllAddresses() {

        List<Address> addresses = new LinkedList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            try {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/addressDB", "root", "root");
                ps = conn.prepareStatement("SELECT * FROM addressDB.addresses;");
                rs = ps.executeQuery();

                while (rs.next()) {
                    Address address = new Address();

                    address.setId(rs.getInt("id"));
                    address.setCountry(rs.getString("country"));
                    address.setCity(rs.getString("city"));
                    address.setStreet(rs.getString("street"));
                    address.setHouseNumber(rs.getString("houseNumber"));
                    addresses.add(address);
                    address.setAddressCounter(address.getId());
                    address.setNumbers(getAddressNumbers(address.getId()));
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        } finally {

            {
                if (conn != null) {
                    conn = null;
                }
                if (ps != null) {
                    ps = null;
                }

                if (rs != null) {
                    rs = null;
                }
            }
        }
        return addresses;
    }

    private List<String> getAddressNumbers(int addressId) {
        List numbers = new ArrayList(5);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try
        {

            try
            {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/addressDB", "root", "root");
                ps = conn.prepareStatement("SELECT number FROM addressDB.contacts WHERE idAddress = " + addressId);
                rs = ps.executeQuery();

                while(rs.next())
                {
                    numbers.add(rs.getString("number"));
                }

            } catch (Exception e)
            {
                e.printStackTrace();
            }
        }
        finally
        {
            if (conn != null) {
                conn = null;
            }
            if (ps != null) {
                ps = null;
            }

            if (rs != null) {
                rs = null;
            }
        }
        return numbers;
    }

    @Override
    public boolean saveAddress(Address address)
    {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement ps = null;

        try
        {
            try
            {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/addressDB", "root", "root");
                ps = conn.prepareStatement("INSERT IGNORE INTO addressDB.addresses(id, country, city, street, houseNumber) VALUES(?, ?, ?, ?, ?)");

                ps.setInt(1, address.getId());
                ps.setString(2, address.getCountry());
                ps.setString(3, address.getCity());
                ps.setString(4, address.getStreet());
                ps.setString(5, address.getHouseNumber());

                int res = ps.executeUpdate();

                if(res > 0)
                {
                    boolean isInsert = saveAddressNumbers(address);
                    if(isInsert)
                    {
                        flag = true;
                    }
                }


            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        finally
        {
            if (conn != null) {
                conn = null;
            }
            if (ps != null) {
                ps = null;
            }


        }
        return flag;
    }
    private boolean saveAddressNumbers(Address address)
    {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        int res = 0;

        try
        {
            try
            {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/addressDB", "root", "root");
                ps = conn.prepareStatement("INSERT IGNORE INTO addressDB.contacts(idAddress, number) VALUES(?, ?)");

                Iterator<String> iter = address.getNumbers().iterator();
                while(iter.hasNext())
                {
                    ps.setInt(1, address.getId());
                    ps.setString(2, iter.next());
                    res = ps.executeUpdate();

                }

                if(res > 0)
                {
                    flag = true;
                }


            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }
        finally
        {
            if (conn != null) {
                conn = null;
            }
            if (ps != null) {
                ps = null;
            }

        }

        return flag;
    }
}
